package dbcreation;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class TableCreator {

	public static void main( String args[] )
	{
		//3
		final String sqlTableUser = "CREATE TABLE USER " +
				"(Login CHAR(50) PRIMARY KEY     NOT NULL," +
				" Password       CHAR(50)        NOT NULL, " + 
				" Role       CHAR(50)	     NOT NULL, " + 
				" Email		     CHAR(50)		 		 , "+
				" Provider CHAR(50) NOT NULL )"; 

		//1
		final String sqlTableProvider = "CREATE TABLE PROVIDER " +
				"(Name CHAR(50) PRIMARY KEY     NOT NULL," +
				" API_URL       TEXT        NOT NULL ) ";

		//4
		final String sqlTableTemplate = " CREATE TABLE TEMPLATE"+
				"(IDTemplate 	INTEGER 	PRIMARY KEY AUTOINCREMENT     NOT NULL," +
				" NameProvider  CHAR(50)        NOT NULL, "+
				" NameTemplate  CHAR(50)        NOT NULL, "+
				" FOREIGN KEY    (NameProvider) REFERENCES PROVIDER(Name) )"; 
		//2
		final String sqlTableRole = " CREATE TABLE ROLE"+
				"(NameRole 		CHAR(50) 	PRIMARY KEY     NOT NULL," +
				" NameProvider  CHAR(50)        NOT NULL, "+
				" NameTemplate  CHAR(50)        NOT NULL, "+
				" FOREIGN KEY    (NameProvider) REFERENCES PROVIDER(Name) )"; 

		//0
		final String sqlTableService = " CREATE TABLE SERVICE"+
				"(NameService 		CHAR(50) 	PRIMARY KEY     NOT NULL,"+
				" NetworkSelected  CHAR(50)        , "+
				" AutoTemplateSelected  CHAR(50)        , "+
				" ManualTemplateSelected  CHAR(50)        , "+
				" AuthenticationSelected  CHAR(50)        , "+
				" IpAdressSelected  CHAR(50)        , "+
				" OsSelected  CHAR(50)        , "+
				" OSID  INT        , "+
				" AutoTemplateID  INT        , "+
				" HDDID  INT        , "+
				" RAMID  INT        , "+
				" SWAPID  INT        , "+
				" CPUID  INT        ) ";

		final String sqlTableServiceAutoTemplate = " CREATE TABLE SERVICE_AUTO_TEMPLATE "+
				" (ID INTEGER PRIMARY KEY NOT NULL ,"+
				" NAME CHAR(50) ,"+
				" ServiceID CHAR(50) NOT NULL )";

		final String sqlTableOSTemplate = " CREATE TABLE OSTEMPLATE "+
				" (ID INTEGER PRIMARY KEY NOT NULL ,"+
				" NAME CHAR(50) ,"+
				" ServiceID CHAR(50) NOT NULL )";

		//5
		final String sqlTableServiceProvider = "CREATE TABLE SERVICE_PROVIDER " +
				" (NameService CHAR(50) PRIMARY KEY     NOT NULL," +
				" PageAssociee TEXT , "+
				" NameProvider  CHAR(50)        NOT NULL, "+
				" FOREIGN KEY    (NameProvider) REFERENCES PROVIDER(Name)," +
				" FOREIGN KEY    (NameService) REFERENCES Service(NameService) )"; 
		//7
		final String sqlTableVM = "CREATE TABLE VM " +
				"(VMID  		INTEGER		 PRIMARY KEY AUTOINCREMENT    NOT NULL," +
				" NodeName  	CHAR(50)    		 	 , " + 
				" RightID   	INT	     			     , " +
				" TemplateOuOS  CHAR(50)    		 	 , " + 
				" HDD		    INT	     			     , " + 
				" RAM		    INT		    		 	 , " + 
				" SWAP		    INT	     			     , " +
				" CPU		    INT		    		 	 , " + 
				" IP_Adress		CHAR(50)   			     NOT NULL, " +
				" Name		    CHAR(50)				 ,"  +
				" State  		CHAR(50)				NOT NULL,"+
				" Login		    CHAR(50)		    	NOT NULL, " +
				" SubnetID		INTEGER		    			, " +
				" FOREIGN KEY    (SubnetID) REFERENCES SUBNET(SubnetID)," +
				" FOREIGN KEY    (Login) REFERENCES USER(Login) )"; 
		//6
		final String sqlTableSubnet = "CREATE TABLE SUBNET " +
				"(SubnetID 		INTEGER		PRIMARY KEY AUTOINCREMENT     NOT NULL," +
				" NumberOfVM 	INT	     			     NOT NULL, " + 
				" Name		 	CHAR(50)   			     , " +
				" Login 		CHAR(50)	     	     NOT NULL, " + 
				" FOREIGN KEY	 (Login) REFERENCES USER(Login) )";
		//8
		final String sqlTableCarcService = "CREATE TABLE CARAC_SERVICE"+
				"(Type 		CHAR(50)		  NOT NULL," +
				" Min   	CHAR(50)     			     , " + 
				" Max	 	CHAR(50)   			         , " +
				" NameService 		CHAR(50)	     	     NOT NULL, " + 
				" Selected 		BOOLEAN	     	         , " + 
				" PRIMARY KEY(Type,NameService),"+
				" FOREIGN KEY	 (NameService) REFERENCES SERVICE(NameService) )";

		final String sqlMinMax = "CREATE TABLE MINMAX "+
				"(ID INTEGER PRIMARY KEY NOT NULL,"+
				"Min CHAR(50) ,"+
				"Max CHAR(50),"+
				"Selected CHAR(50) )";

		final String sqlEndpoint="CREATE TABLE ENDPOINT"+
				"( ID INTEGER PRIMARY KEY NOT NULL,"+
				"NameEndpoint CHAR(50) NOT NULL,"+
				"ContentEndpoint CHAR(50) NOT NULL,"+
				"ProviderName CHAR(50) NOT NULL);";

		Connection c = null;
		Statement stmt = null;
		try {
			Class.forName("org.sqlite.JDBC");
			c = DriverManager.getConnection("jdbc:sqlite:becloud.db");
			System.out.println("Opened database successfully");

			stmt = c.createStatement();

			//Executer les statement i.e. creer les tables
			//J'ai cree les tables une par une au cas ou il y ait des erreurs ...
			stmt.executeUpdate(sqlEndpoint);


			stmt.close();
			c.close();
		} catch ( Exception e ) {
			System.err.println( e.getClass().getName() + ": " + e.getMessage() );
			System.exit(0);
		}
		System.out.println("Table created successfully");
	}

}